-- Emobot Database Initialization Script
-- This script creates the initial database structure

-- Create UUID extension if not exists
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS user_profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    avatar VARCHAR(500),
    age INTEGER,
    gender VARCHAR(50),
    region_cultural_background TEXT,
    occupation_education TEXT,
    emotional_expression_style TEXT,
    privacy_tech_acceptance TEXT,
    professional_role TEXT,
    family_role TEXT,
    social_frequency TEXT,
    family_structure TEXT,
    living_status TEXT,
    relationship_status TEXT,
    life_turning_points TEXT,
    personality JSONB,
    current_emotion VARCHAR(100),
    emotion_list JSONB,
    plutchik_emotion_wheel JSONB,
    emotional_continuity TEXT,
    interaction_style TEXT,
    interaction_depth_voice TEXT,
    usage_time_period TEXT,
    interaction_frequency TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Migration/compatibility: ensure required columns exist on older databases
-- These are safe no-op if columns already exist
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS personality JSONB;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS current_emotion TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS emotion_list JSONB;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS plutchik_emotion_wheel JSONB;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS emotional_continuity TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS interaction_style TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS interaction_depth_voice TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS usage_time_period TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS interaction_frequency TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS region_cultural_background TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS occupation_education TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS emotional_expression_style TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS privacy_tech_acceptance TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS professional_role TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS family_role TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS social_frequency TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS family_structure TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS living_status TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS relationship_status TEXT;
ALTER TABLE user_profiles ADD COLUMN IF NOT EXISTS life_turning_points TEXT;

-- Create conversation_histories table
CREATE TABLE IF NOT EXISTS conversation_histories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL,
    session_id VARCHAR(255) NOT NULL,
    user_message TEXT NOT NULL,
    bot_response TEXT,
    image_url VARCHAR(1000),
    intent VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create environment_memories table
CREATE TABLE IF NOT EXISTS environment_memories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL,
    location VARCHAR(255),
    time TIMESTAMP WITH TIME ZONE,
    context TEXT,
    important_events TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create face_embeddings table
CREATE TABLE IF NOT EXISTS face_embeddings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id VARCHAR(255) NOT NULL,
    face_embedding BYTEA NOT NULL,
    face_url VARCHAR(1000) NOT NULL,
    face_image_path VARCHAR(1000) NOT NULL,
    embedding_dimension INTEGER NOT NULL,
    confidence_score FLOAT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create scenes table
CREATE TABLE IF NOT EXISTS scenes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    scene VARCHAR(255) NOT NULL,
    platform VARCHAR(255) NOT NULL,
    items JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_user_profiles_user_id ON user_profiles(user_id);
CREATE INDEX IF NOT EXISTS idx_conversation_histories_user_id ON conversation_histories(user_id);
CREATE INDEX IF NOT EXISTS idx_conversation_histories_session_id ON conversation_histories(session_id);
CREATE INDEX IF NOT EXISTS idx_environment_memories_user_id ON environment_memories(user_id);
CREATE INDEX IF NOT EXISTS idx_face_embeddings_user_id ON face_embeddings(user_id);
CREATE INDEX IF NOT EXISTS idx_scenes_scene ON scenes(scene);
CREATE INDEX IF NOT EXISTS idx_scenes_platform ON scenes(platform);

-- Grant permissions to the application user
GRANT ALL PRIVILEGES ON DATABASE emobot TO myusr;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myusr;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myusr;

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create triggers for updated_at
DROP TRIGGER IF EXISTS update_user_profiles_updated_at ON user_profiles;
CREATE TRIGGER update_user_profiles_updated_at 
    BEFORE UPDATE ON user_profiles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_environment_memories_updated_at ON environment_memories;
CREATE TRIGGER update_environment_memories_updated_at 
    BEFORE UPDATE ON environment_memories 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_face_embeddings_updated_at ON face_embeddings;
CREATE TRIGGER update_face_embeddings_updated_at 
    BEFORE UPDATE ON face_embeddings 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_scenes_updated_at ON scenes;
CREATE TRIGGER update_scenes_updated_at 
    BEFORE UPDATE ON scenes 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Insert default admin user (optional)
INSERT INTO user_profiles (user_id, name) 
VALUES ('admin', 'System Administrator')
ON CONFLICT (user_id) DO NOTHING;

-- Print completion message
DO $$
BEGIN
    RAISE NOTICE '🎉 Emobot database initialization completed successfully!';
    RAISE NOTICE 'Created tables: user_profiles, conversation_histories, environment_memories, face_embeddings, scenes';
END $$;